package com.app.util;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class ExcelNewUtil {

    private String                    filepath;
    private List<Map<String, String>> colsConfig = new ArrayList<Map<String, String>>();
    private List<List<String>>        list       = new ArrayList<List<String>>();

    public String wirte() throws IOException, RowsExceededException, WriteException {
        OutputStream os = new FileOutputStream(this.filepath);
        WritableWorkbook workbook = Workbook.createWorkbook(os);// 创建工作区
        WritableSheet sheet = workbook.createSheet("sheet1", 0);// 创建sheet

        // 设置字体;
        WritableFont font1 = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, false,
                                              UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
        WritableFont font2 = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false,
                                              UnderlineStyle.NO_UNDERLINE, Colour.BLACK);

        WritableCellFormat cellFormat1 = new WritableCellFormat(font1);
        WritableCellFormat cellFormat2 = new WritableCellFormat(font2);

        // 设置背景颜色;
        cellFormat1.setBackground(Colour.GRAY_25);
        // 设置自动换行;
        cellFormat1.setWrap(true);
        // 设置边框;
        cellFormat1.setBorder(Border.ALL, BorderLineStyle.THIN);
        // 设置文字居中对齐方式;
        cellFormat1.setAlignment(Alignment.CENTRE);
        // 设置垂直居中;
        cellFormat1.setVerticalAlignment(VerticalAlignment.CENTRE);

        // 设置自动换行;
        cellFormat2.setWrap(true);
        // 设置边框;
        cellFormat2.setBorder(Border.ALL, BorderLineStyle.THIN);
        // 设置文字居中对齐方式;
        cellFormat2.setAlignment(Alignment.CENTRE);
        // 设置垂直居中;
        cellFormat2.setVerticalAlignment(VerticalAlignment.CENTRE);

        // 开始读取配置文件生成表头
        for (int i = 0; i < this.colsConfig.size(); i++) {
            Map<String, String> map = this.colsConfig.get(i);
            String title = map.get("title");
            String width = map.get("width");
            sheet.addCell(new Label(i, 0, title, cellFormat1));// Lable第一个参数是列，第二个参数是行，第三个参数是文字
            sheet.setColumnView(i, Integer.parseInt(width));
            sheet.setRowView(0, 500);
        }

        // 循环生成数据，必须要与表头的数据对应
        for (int i = 0; i < list.size(); i++) {
            int index = i + 1;
            // 设置列
            List<String> col = list.get(i);
            for (int j = 0; j < col.size(); j++) {
            	sheet.addCell(new Label(j, index, col.get(j), cellFormat2));
            }
        }
        workbook.write();// 将内容写到输出流中，然后关闭工作区，最后关闭输出流
        workbook.close();
        os.close();
        return filepath;
    }

    public void addList(List list) {
        this.list.add(list);
    }

    public void addcolsconfig(String title, int width) {
        Map<String, String> m = new HashMap<String, String>();
        m.put("title", title);
        m.put("width", width + "");
        colsConfig.add(m);
    }

    public List<Map<String, String>> getColsConfig() {
        return this.colsConfig;
    }

    public ExcelNewUtil(String filepath){
        super();
        this.filepath = filepath;
    }

    public ExcelNewUtil(){
        super();
    }

    public List<String> readExcel(InputStream is) throws BiffException, IOException {
        List<String> strList = new ArrayList<String>();
        // 2、声明工作簿对象
        Workbook rwb = Workbook.getWorkbook(is);
        // 3、获得工作簿的个数,对应于一个excel中的工作表个数
        rwb.getNumberOfSheets();

        Sheet oFirstSheet = rwb.getSheet(0);// 使用索引形式获取第一个工作表，也可以使用rwb.getSheet(sheetName);其中sheetName表示的是工作表的名称
        // System.out.println("工作表名称：" + oFirstSheet.getName());
        int rows = oFirstSheet.getRows();// 获取工作表中的总行数
        int columns = oFirstSheet.getColumns();// 获取工作表中的总列数
        for (int i = 0; i < rows; i++) {
            String str = new String();
            for (int j = 0; j < columns; j++) {
                Cell oCell = oFirstSheet.getCell(j, i);// 需要注意的是这里的getCell方法的参数，第一个是指定第几列，第二个参数才是指定第几行
                if (columns - j == 1) {
                    str = str + oCell.getContents();
                } else {
                    str = str + oCell.getContents() + ",";
                }
            }
            strList.add(str);
        }
        return strList;
    }
}
